const express = require('express');
const router = express.Router();
const mysql = require('mysql');
const bcrypt = require('bcrypt');
const constant = require('../data/constant');
const auth = require('../middleware/auth');
const project_auth = require('../middleware/project_auth');
const tools = require('../data/tools');

// 新建项目接口
router.post('/new_project',auth, (req, res) => {
    var Pool = mysql.createPool({
        'host':constant.DB_HOST_NAME,
        'user':constant.DB_USER,
        'password':constant.DB_PWD,
        'database':constant.DB_NAME,
        'multipleStatements': true // 支持执行多条 sql 语句
    });
    Pool.getConnection( (err,connection) => {
        if(err){
            console.log(err);
            res.send({
                'res':{
                    'ok': 0,
                    'msg':'数据库连接失败'
                }
            });
        }else{
            // 构造sql语句
            let  sql = "INSERT INTO project(`name`,`desc`,`tags`,`course`,`start_time`,`end_time`,`sponsor`,`enterprise_tag`,`username`)"+
            "VALUES(?,?,?,?,?,?,?,?,?);"
            let type = false;
            if(req.body.type === 2){
                type = true;
            }
            // console.log(req.body);
            let  sqlParams = [
                req.body.name,
                req.body.desc,
                req.body.tags,
                req.body.course,
                req.body.start_time,
                req.body.end_time,
                req.body.uid,
                type,
                req.body.username
            ];
            // console.log(sqlParams);
            connection.query(sql,sqlParams,async (err,data) => {
                if(err){
                    console.log(err);
                    res.statusCode = 500;
                    res.send({
                        'res':{
                            'ok': 0,
                            'msg':'数据库连接失败'
                        }
                    });
                }else{
                    let res_data = JSON.stringify(data);//把data对象转为字符串，去掉RowDataPacket
                    res_data = JSON.parse(res_data);//把results字符串转为json对象
                    // console.log(res_data);
                    
                    if(res_data.affectedRows == 1){
                        let  sql = "insert into project_member(pid,uid,username,isOwner) values(?,?,?,?);";
                        let  sqlParams = [
                            res_data.insertId,
                            req.body.uid,
                            req.body.username,
                            true
                        ];
                        // console.log(sql,sqlParams);
                        connection.query(sql,sqlParams,async (err,data) => {
                            if(err){
                                console.log(err);
                                res.statusCode = 500;
                                res.send({
                                    'res':{
                                        'ok': 0,
                                        'msg':'数据库连接失败'
                                    }
                                });
                            }else{
                                let res_data = JSON.stringify(data);//把data对象转为字符串，去掉RowDataPacket
                                res_data = JSON.parse(res_data);//把results字符串转为json对象
                                // console.log(res_data);

                                if(res_data.affectedRows == 1){
                                    res.send({
                                        res:{
                                            ok:1,
                                            msg:"项目创建成功！！"
                                        }
                                    });
                                }else{
                                    res.send({
                                        res:{
                                            ok:0,
                                            msg:"项目创建失败，请联系管理员！"
                                        }
                                    })
                                }
                            }
                        });
                    }else{
                        res.send({
                            res:{
                                ok:0,
                                msg:"项目创建失败，请联系管理员！"
                            }
                        })
                    }
                }
            });
            connection.release();
        }
    });
});

// 获取我创建的项目接口
router.post('/my_project',auth, (req, res) => {
    var Pool = mysql.createPool({
        'host':constant.DB_HOST_NAME,
        'user':constant.DB_USER,
        'password':constant.DB_PWD,
        'database':constant.DB_NAME,
        'multipleStatements': true // 支持执行多条 sql 语句
    });
    Pool.getConnection( (err,connection) => {
        if(err){
            console.log(err);
            res.send({
                'res':{
                    'ok': 0,
                    'msg':'数据库连接失败'
                }
            });
        }else{
            // 构造sql语句
            let  sql = "SELECT * FROM project WHERE sponsor = ?"
            let  sqlParams = [
                req.body.uid,
            ];
            connection.query(sql,sqlParams,async (err,data) => {
                if(err){
                    console.log(err);
                    res.statusCode = 500;
                    res.send({
                        'res':{
                            'ok': 0,
                            'msg':'数据库连接失败'
                        }
                    });
                }else{
                    let res_data = JSON.stringify(data);//把data对象转为字符串，去掉RowDataPacket
                    res_data = JSON.parse(res_data);//把results字符串转为json对象
                    
                    if(res_data.length >= 1){
                        // 处理数据
                        let result = [];
                        for(let i in res_data){
                            let temp = {
                                'id':res_data[i].pid,
                                'name':res_data[i].name,
                                'course':res_data[i].course,
                                'isFinished':res_data[i].isFinished,
                                'enterpriseProject':res_data[i].enterprise_tag
                            }
                            result.push(temp);
                        }
                        res.send({
                            res:{
                                ok:1,
                                data:result
                            }
                        });
                    }else{
                        res.send({
                            res:{
                                ok:0,
                                msg:"您没有创建任何项目！"
                            }
                        })
                    }
                }
            });
            connection.release();
        }
    });
});
// 获取我参与的项目接口
router.post('/other_project',auth, (req, res) => {
    var Pool = mysql.createPool({
        'host':constant.DB_HOST_NAME,
        'user':constant.DB_USER,
        'password':constant.DB_PWD,
        'database':constant.DB_NAME,
        'multipleStatements': true // 支持执行多条 sql 语句
    });
    Pool.getConnection( (err,connection) => {
        if(err){
            console.log(err);
            res.send({
                'res':{
                    'ok': 0,
                    'msg':'数据库连接失败'
                }
            });
        }else{
            // 构造sql语句
            let  sql = "SELECT * FROM project where pid in (SELECT DISTINCT pid FROM project_member WHERE uid = ? and isOwner = ?);"
            let  sqlParams = [
                req.body.uid,
                false
            ];
            connection.query(sql,sqlParams,async (err,data) => {
                if(err){
                    console.log(err);
                    res.statusCode = 500;
                    res.send({
                        'res':{
                            'ok': 0,
                            'msg':'数据库连接失败'
                        }
                    });
                }else{
                    let res_data = JSON.stringify(data);//把data对象转为字符串，去掉RowDataPacket
                    res_data = JSON.parse(res_data);//把results字符串转为json对象
                    if(res_data.length >= 1){
                        // 处理数据
                        let result = [];
                        for(let i in res_data){
                            let temp = {
                                'id':res_data[i].pid,
                                'name':res_data[i].name,
                                'course':res_data[i].course,
                                'isFinished':res_data[i].isFinished,
                                'enterpriseProject':res_data[i].enterprise_tag
                            }
                            result.push(temp);
                        }
                        res.send({
                            res:{
                                ok:1,
                                data:result
                            }
                        });
                    }else{
                        res.send({
                            res:{
                                ok:0,
                                msg:"您没有参与任何项目！"
                            }
                        })
                    }
                }
            });
            connection.release();
        }
    });
});
// 获取项目详细信息的接口
router.get('/project_detail',auth,project_auth, (req, res) => {
    var Pool = mysql.createPool({
        'host':constant.DB_HOST_NAME,
        'user':constant.DB_USER,
        'password':constant.DB_PWD,
        'database':constant.DB_NAME,
        'multipleStatements': true // 支持执行多条 sql 语句
    });
    Pool.getConnection( (err,connection) => {
        if(err){
            console.log(err);
            res.send({
                'res':{
                    'ok': 0,
                    'msg':'数据库连接失败'
                }
            });
        }else{
            // 构造sql语句
            let  sql = "SELECT * FROM project WHERE pid = ?;"+
            "SELECT * FROM project_member WHERE pid = ?";
            let  sqlParams = [
                req.query.project_id,
                req.query.project_id
            ];
            connection.query(sql,sqlParams,async (err,data) => {
                if(err){
                    console.log(err);
                    res.statusCode = 500;
                    res.send({
                        'res':{
                            'ok': 0,
                            'msg':'数据库连接失败'
                        }
                    });
                }else{
                    let res_data = JSON.stringify(data);//把data对象转为字符串，去掉RowDataPacket
                    res_data = JSON.parse(res_data);//把results字符串转为json对象
                    
                    if(res_data[0].length >= 1){
                        // 处理数据
                        let result = {
                            'name':res_data[0][0].name,
                            'desc':res_data[0][0].desc,
                            'start_time':tools.convert_time(res_data[0][0].start_time),
                            'end_time':tools.convert_time(res_data[0][0].end_time),
                            'course':res_data[0][0].course,
                            'status':!res_data[0][0].isFinished,
                            'member':[]
                        };
                        for(let i in res_data[1]){
                            let temp = {
                                'id':res_data[1][i].uid,
                                'name':res_data[1][i].username,
                                'isOwner':res_data[1][i].isOwner
                            }
                            result.member.push(temp);
                        }
                        res.send({
                            res:{
                                ok:1,
                                data:result
                            }
                        });
                    }else{
                        res.send({
                            res:{
                                ok:0,
                                msg:"获取项目信息失败，请稍后重试！"
                            }
                        })
                    }
                }
            });
            connection.release();
        }
    });
});
 
// 邀请项目成员的接口
router.post('/invate',auth, (req, res) => {
    var Pool = mysql.createPool({
        'host':constant.DB_HOST_NAME,
        'user':constant.DB_USER,
        'password':constant.DB_PWD,
        'database':constant.DB_NAME,
        'multipleStatements': true // 支持执行多条 sql 语句
    });
    Pool.getConnection( (err,connection) => {
        if(err){
            console.log(err);
            res.send({
                'res':{
                    'ok': 0,
                    'msg':'数据库连接失败'
                }
            });
        }else{
            let  sql = "SELECT uid FROM user where username = ?"
            let  sqlParams = [
                req.body.username
            ];
            connection.query(sql,sqlParams,async (err,data) => {
                if(err){
                    console.log(err);
                    res.statusCode = 500;
                    res.send({
                        'res':{
                            'ok': 0,
                            'msg':'数据库连接失败'
                        }
                    });
                }else{
                    let res_data = JSON.stringify(data);//把data对象转为字符串，去掉RowDataPacket
                    res_data = JSON.parse(res_data);//把results字符串转为json对象
                    // console.log(res_data);
                    if(res_data.length >= 1){
                        // 往成员表中插入数据
                        // 构造sql语句
                        let  sql = "INSERT INTO project_member(pid,uid,username) VALUES(?,?,?);"
                        let  sqlParams = [
                            req.body.project_id,
                            res_data[0].uid,
                            req.body.username
                        ];
                        connection.query(sql,sqlParams,async(err,data) => {
                            if(err){
                                console.log(err);
                                // 用户已经是项目成员的情况
                                if(err.errno == 1062){
                                    res.send({
                                        res:{
                                            ok:0,
                                            msg:"该用户已经是本项目成员，请勿重复邀请！"
                                        }
                                    })
                                    return;
                                }
                                res.statusCode = 500;
                                res.send({
                                    'res':{
                                        'ok': 0,
                                        'msg':'数据库连接失败'
                                    }
                                });
                            }else{
                                let res_data = JSON.stringify(data);//把data对象转为字符串，去掉RowDataPacket
                                res_data = JSON.parse(res_data);//把results字符串转为json对象
                                // console.log(res_data);
                                if(res_data.affectedRows == 1){
                                    res.send({
                                        res:{
                                            ok:1,
                                            msg:'邀请成功！'
                                        }
                                    });
                                }else{
                                    res.send({
                                        res:{
                                            ok:0,
                                            msg:"出错了！邀请失败，可能的原因是该用户已经是本项目成员"
                                        }
                                    })
                                }
                            }
                        })
                    }else{
                        res.send({
                            res:{
                                ok:0,
                                msg:"出错了！该用户不存在，请确认后重试！"
                            }
                        })
                    }
                }
            });
            connection.release();
        }
    });
});

// 新建文件夹的接口
router.post('/add_folder',auth, (req, res) => {
    var Pool = mysql.createPool({
        'host':constant.DB_HOST_NAME,
        'user':constant.DB_USER,
        'password':constant.DB_PWD,
        'database':constant.DB_NAME,
        'multipleStatements': true // 支持执行多条 sql 语句
    });
    Pool.getConnection( (err,connection) => {
        if(err){
            console.log(err);
            res.send({
                'res':{
                    'ok': 0,
                    'msg':'数据库连接失败'
                }
            });
        }else{
            let  sql = "INSERT INTO folder(name,lastlevel,project_tag,project) values(?,?,?,?)"
            let  sqlParams = [
                req.body.name,
                req.body.lastlevel,
                true,
                req.body.project_id
            ];
            connection.query(sql,sqlParams,async (err,data) => {
                if(err){
                    console.log(err);
                    res.statusCode = 500;
                    res.send({
                        'res':{
                            'ok': 0,
                            'msg':'数据库连接失败'
                        }
                    });
                }else{
                    let res_data = JSON.stringify(data);//把data对象转为字符串，去掉RowDataPacket
                    res_data = JSON.parse(res_data);//把results字符串转为json对象
                    // console.log(res_data);
                    if(res_data.affectedRows == 1){
                        res.send({
                            res:{
                                ok:1,
                                msg:"添加文件夹成功！"
                            }
                        })
                    }else{
                        res.send({
                            res:{
                                ok:0,
                                msg:"出错了！"
                            }
                        })
                    }
                }
            });
            connection.release();
        }
    });
});
// 完成项目的接口
router.post('/finish',auth, (req, res) => {
    var Pool = mysql.createPool({
        'host':constant.DB_HOST_NAME,
        'user':constant.DB_USER,
        'password':constant.DB_PWD,
        'database':constant.DB_NAME,
        'multipleStatements': true // 支持执行多条 sql 语句
    });
    Pool.getConnection( (err,connection) => {
        if(err){
            console.log(err);
            res.send({
                'res':{
                    'ok': 0,
                    'msg':'数据库连接失败'
                }
            });
        }else{
            let  sql = "update project set isFinished = ? where pid = ?;";
            let  sqlParams = [
                true,
                parseInt(req.body.project_id),
            ];
            connection.query(sql,sqlParams,async (err,data) => {
                if(err){
                    console.log(err);
                    res.statusCode = 500;
                    res.send({
                        'res':{
                            'ok': 0,
                            'msg':'数据库连接失败'
                        }
                    });
                }else{
                    let res_data = JSON.stringify(data);//把data对象转为字符串，去掉RowDataPacket
                    res_data = JSON.parse(res_data);//把results字符串转为json对象
                    // console.log(res_data);
                    if(res_data.affectedRows == 1){
                        res.send({
                            res:{
                                ok:1,
                                msg:"项目资产申报成功！"
                            }
                        })
                    }else{
                        res.send({
                            res:{
                                ok:0,
                                msg:"出错了！"
                            }
                        })
                    }
                }
            });
            connection.release();
        }
    });
});

// 设为企业项目的接口
router.post('/setEnterprise',auth, (req, res) => {
    var Pool = mysql.createPool({
        'host':constant.DB_HOST_NAME,
        'user':constant.DB_USER,
        'password':constant.DB_PWD,
        'database':constant.DB_NAME,
        'multipleStatements': true // 支持执行多条 sql 语句
    });
    Pool.getConnection( (err,connection) => {
        if(err){
            console.log(err);
            res.send({
                'res':{
                    'ok': 0,
                    'msg':'数据库连接失败'
                }
            });
        }else{
            let  sql = "update project set enterprise_tag = ? where pid = ?;";
            let  sqlParams = [
                true,
                parseInt(req.body.project_id),
            ];
            connection.query(sql,sqlParams,async (err,data) => {
                if(err){
                    console.log(err);
                    res.statusCode = 500;
                    res.send({
                        'res':{
                            'ok': 0,
                            'msg':'数据库连接失败'
                        }
                    });
                }else{
                    let res_data = JSON.stringify(data);//把data对象转为字符串，去掉RowDataPacket
                    res_data = JSON.parse(res_data);//把results字符串转为json对象
                    // console.log(res_data);
                    if(res_data.affectedRows == 1){
                        res.send({
                            res:{
                                ok:1,
                                msg:"企业项目设置成功！"
                            }
                        })
                    }else{
                        res.send({
                            res:{
                                ok:0,
                                msg:"出错了！"
                            }
                        })
                    }
                }
            });
            connection.release();
        }
    });
});

module.exports = router;